package com.efast.cafe.framework.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.efast.cafe.framework.jdbc.bean.DBColumn;
import com.efast.cafe.framework.jdbc.bean.DBTable;
import com.efast.cafe.framework.jdbc.bean.JdbcTypeResolveToLov;
import com.efast.cafe.framework.jdbc.persistence.BoundSql;
import com.efast.cafe.framework.jdbc.persistence.Criteria;
import com.efast.cafe.framework.jdbc.persistence.DefaultNameHandler;
import com.efast.cafe.framework.jdbc.persistence.MyBeanPropertyRowMapper;
import com.efast.cafe.framework.jdbc.persistence.NameHandler;
import com.efast.cafe.framework.jdbc.persistence.SqlAssembleUtils;
import com.efast.cafe.framework.jdbc.util.NameUtils;
import com.efast.cafe.framework.pager.Pagination;
import com.efast.cafe.util.ClassUtils;
import com.efast.cafe.util.ConfigUtil;

/**
 * jdbc操作dao
 *
 * Created by xuanming.feng
 */
@Repository
public class JdbcDaoImpl implements JdbcDao {

	protected final Logger log = LoggerFactory.getLogger(getClass());

	/** spring jdbcTemplate 对象 */
	protected JdbcTemplate jdbcTemplate;

	/** 名称处理器，为空按默认执行 */
	protected NameHandler nameHandler;

	/** rowMapper，为空按默认执行 */
	protected String rowMapperClass;

	/** 数据库方言 */
	protected String dialect=ConfigUtil.getValue("cafe.db.dialect");

	/**
	 * 插入数据
	 *
	 * @param entity
	 *          the entity
	 * @param criteria
	 *          the criteria
	 * @return long long
	 */
	private Long insert(Object entity, Criteria criteria) {
		Class<?> entityClass = SqlAssembleUtils.getEntityClass(entity, criteria);
		NameHandler handler = this.getNameHandler();
		String pkValue = handler.getPKValue(entityClass, this.getDialect());
		if (StringUtils.isNotBlank(pkValue)) {
			String primaryName = handler.getPKName(entityClass);
			if (criteria == null) {
				criteria = Criteria.create(entityClass);
			}
			criteria.setPKValueName(NameUtils.getCamelName(primaryName), pkValue);
		}
		final BoundSql boundSql = SqlAssembleUtils.buildInsertSql(entity, criteria,
		    this.getNameHandler());

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(boundSql.getSql(),
		        new String[] { boundSql.getPrimaryKey() });
				int index = 0;
				for (Object param : boundSql.getParams()) {
					index++;
					ps.setObject(index, param);
				}
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().longValue();
	}

	@Override
	public Long insert(Object entity) {
		return this.insert(entity, null);
	}

	@Override
	public Long insert(Criteria criteria) {
		return this.insert(null, criteria);
	}

	@Override
	public void save(Object entity) {
		final BoundSql boundSql = SqlAssembleUtils.buildInsertSql(entity, null,
		    this.getNameHandler());
		jdbcTemplate.update(boundSql.getSql(), boundSql.getParams().toArray());
	}

	@Override
	public void save(Criteria criteria) {
		final BoundSql boundSql = SqlAssembleUtils.buildInsertSql(null, criteria,
		    this.getNameHandler());
		jdbcTemplate.update(boundSql.getSql(), boundSql.getParams().toArray());
	}

	@Override
	public void update(Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildUpdateSql(null, criteria, this.getNameHandler());
		jdbcTemplate.update(boundSql.getSql(), boundSql.getParams().toArray());
	}

	@Override
	public void update(Object entity) {
		BoundSql boundSql = SqlAssembleUtils.buildUpdateSql(entity, null, this.getNameHandler());
		jdbcTemplate.update(boundSql.getSql(), boundSql.getParams().toArray());
	}

	@Override
	public void delete(Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildDeleteSql(null, criteria, this.getNameHandler());
		jdbcTemplate.update(boundSql.getSql(), boundSql.getParams().toArray());
	}

	@Override
	public void delete(Object entity) {
		BoundSql boundSql = SqlAssembleUtils.buildDeleteSql(entity, null, this.getNameHandler());
		jdbcTemplate.update(boundSql.getSql(), boundSql.getParams().toArray());
	}

	@Override
	public void delete(Class<?> clazz, Long id) {
		BoundSql boundSql = SqlAssembleUtils.buildDeleteSql(clazz, id, this.getNameHandler());
		jdbcTemplate.update(boundSql.getSql(), boundSql.getParams().toArray());
	}

	@Override
	public void deleteAll(Class<?> clazz) {
		String tableName = this.getNameHandler().getTableName(clazz);
		String sql = "TRUNCATE TABLE " + tableName;
		jdbcTemplate.execute(sql);
	}

	@Override
	public <T> List<T> queryList(Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildListSql(null, criteria, this.getNameHandler());
		List<?> list = jdbcTemplate.query(boundSql.getSql(), boundSql.getParams().toArray(),
		    this.getRowMapper(criteria.getEntityClass()));
		return (List<T>) list;
	}

	@Override
	public <T> List<T> queryList(T entity) {
		BoundSql boundSql = SqlAssembleUtils.buildListSql(entity, null, this.getNameHandler());
		List<?> list = jdbcTemplate.query(boundSql.getSql(), boundSql.getParams().toArray(),
		    this.getRowMapper(entity.getClass()));
		return (List<T>) list;
	}

	@Override
	public <T> List<T> queryList(T entity, Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildListSql(entity, criteria, this.getNameHandler());
		List<?> list = jdbcTemplate.query(boundSql.getSql(), boundSql.getParams().toArray(),
		    this.getRowMapper(entity.getClass()));
		return (List<T>) list;
	}

	@Override
	public <T> Pagination<T> queryListByPage(Pagination<T> page, Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildListSql(null, criteria, this.getNameHandler());

		StringBuffer totalSQL = new StringBuffer(" SELECT count(1) FROM ( ");
		totalSQL.append(boundSql.getSql());
		totalSQL.append(" ) totalTable ");

		// 总记录数
		page.setTotalRows(jdbcTemplate.queryForObject(totalSQL.toString(), boundSql.getParams().toArray(),Integer.class));

		// 计算总页数
		page.setTotalPages();
		// 计算起始行数
		page.setStartIndex();
		// 计算结束行数
		page.setLastIndex();

		String pageSql = "";

		if (this.getDialect().equalsIgnoreCase("mysql")) {
			// mysql 分页
			pageSql = getMySQLPageSQL(boundSql.getSql(), page.getStartIndex(), page.getNumPerPage());
		} else if (this.getDialect().equalsIgnoreCase("oracle")) {
			// oracle 分页sql
			pageSql = getOraclePageSQL(boundSql.getSql(), page.getStartIndex(), page.getNumPerPage());
		}

		List<?> list = jdbcTemplate.query(pageSql, boundSql.getParams().toArray(),
		    this.getRowMapper(criteria.getEntityClass()));
		List<T> res = (List<T>) list;
		page.setResultList(res);
		return page;
	}

	public <T> Pagination<T> queryListByPage(Pagination<T> page, T entity) {
		BoundSql boundSql = SqlAssembleUtils.buildListSql(entity, null, this.getNameHandler());
		StringBuffer totalSQL = new StringBuffer(" SELECT count(1) FROM ( ");
		totalSQL.append(boundSql.getSql());
		totalSQL.append(" ) totalTable ");

		// 总记录数
		page.setTotalRows(jdbcTemplate.queryForObject(totalSQL.toString(), boundSql.getParams().toArray(),Integer.class));

		// 计算总页数
		page.setTotalPages();
		// 计算起始行数
		page.setStartIndex();
		// 计算结束行数
		page.setLastIndex();

		String pageSql = "";
		if (this.getDialect().equalsIgnoreCase("mysql")) {
			// mysql 分页
			pageSql = getMySQLPageSQL(boundSql.getSql(), page.getStartIndex(), page.getNumPerPage());
		} else if (this.getDialect().equalsIgnoreCase("oracle")) {
			// oracle 分页sql
			pageSql = getOraclePageSQL(boundSql.getSql(), page.getStartIndex(), page.getNumPerPage());
		}

		List<?> list = jdbcTemplate.query(pageSql, boundSql.getParams().toArray(),
		    this.getRowMapper(entity.getClass()));
		List<T> res = (List<T>) list;
		page.setResultList(res);
		return page;
	}

	public <T> Pagination<T> queryListByPage(Pagination<T> page, String boundSql, List<Object> params, Class<T> clazz) {
		StringBuffer totalSQL = new StringBuffer(" SELECT count(1) FROM ( ");
		totalSQL.append(boundSql);
		totalSQL.append(" ) totalTable ");

		// 总记录数
		page.setTotalRows(jdbcTemplate.queryForObject(totalSQL.toString(), params != null ? params.toArray() : null,Integer.class));

		// 计算总页数
		page.setTotalPages();
		// 计算起始行数
		page.setStartIndex();
		// 计算结束行数
		page.setLastIndex();

		String pageSql = "";
		if (this.getDialect().equalsIgnoreCase("mysql")) {
			// mysql 分页
			pageSql = getMySQLPageSQL(boundSql, page.getStartIndex(), page.getNumPerPage());
		} else if (this.getDialect().equalsIgnoreCase("oracle")) {
			// oracle 分页sql
			pageSql = getOraclePageSQL(boundSql, page.getStartIndex(), page.getNumPerPage());
		}

		List<?> list = jdbcTemplate.query(pageSql, params != null ? params.toArray() : null,
		    this.getRowMapper(clazz));
		List<T> res = (List<T>) list;
		page.setResultList(res);
		return page;
	}

	@Override
	public List<Map<String, Object>> queryMapListByPage(Integer page,
	    Integer pageSize, String sql, List<Object> params) {

		String pageSql = "";
		if (this.getDialect().equalsIgnoreCase("mysql")) {
			// mysql 分页
			pageSql = getMySQLPageSQL(sql, page, pageSize);
		} else if (this.getDialect().equalsIgnoreCase("oracle")) {
			// oracle 分页sql
			pageSql = getOraclePageSQL(sql, page, pageSize);
		}
		return jdbcTemplate.queryForList(pageSql, null != params ? params.toArray() : null);
	}

	/**
	 * 构造MySQL数据分页SQL
	 * 
	 * @param queryString
	 * @param startIndex
	 * @param pageSize
	 * @return
	 */
	private String getMySQLPageSQL(String queryString, Integer startIndex, Integer pageSize) {
		String result = "";
		if (null != startIndex && null != pageSize) {
			result = queryString + " limit " + startIndex + "," + pageSize;
		} else if (null != startIndex && null == pageSize) {
			result = queryString + " limit " + startIndex;
		} else {
			result = queryString;
		}
		return result;
	}

	private String getOraclePageSQL(String queryString, Integer startIndex, Integer pageSize) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer.append(" Select main.* ");
		queryBuffer.append(" From  ");
		queryBuffer.append(" (Select t.*,rownum rn  ");
		queryBuffer.append("  from ( ");
		queryBuffer.append(queryString);
		queryBuffer.append("       ) t  ");
		queryBuffer.append(" ) main ");
		queryBuffer.append(" where main.rn > ");
		queryBuffer.append(startIndex);
		queryBuffer.append(" and main.rn <=");
		queryBuffer.append(startIndex + pageSize);
		return queryBuffer.toString();
	}

	@Override
	public int queryCount(Object entity, Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildCountSql(entity, criteria, this.getNameHandler());
		return jdbcTemplate.queryForObject(boundSql.getSql(), boundSql.getParams().toArray(),Integer.class);
	}

	@Override
	public int queryCount(Object entity) {
		BoundSql boundSql = SqlAssembleUtils.buildCountSql(entity, null, this.getNameHandler());
		return jdbcTemplate.queryForObject(boundSql.getSql(), boundSql.getParams().toArray(),Integer.class);
	}

	@Override
	public int queryCount(Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildCountSql(null, criteria, this.getNameHandler());
		return jdbcTemplate.queryForObject(boundSql.getSql(), boundSql.getParams().toArray(),Integer.class);
	}

	@Override
	public int queryCount(String sql) {
		StringBuffer sb = new StringBuffer("select count(1) from (" + sql + ") t");
		return jdbcTemplate.queryForObject(sb.toString(),Integer.class);
	}

	@Override
	public int queryCount(String sql, Object[] params) {
		StringBuffer sb = new StringBuffer("select count(1) from (" + sql + ") t");
		
		return jdbcTemplate.queryForObject(sb.toString(), params,Integer.class);
	}

	@Override
	public <T> T get(Class<T> clazz, Long id) {
		BoundSql boundSql = SqlAssembleUtils.buildByIdSql(clazz, id, null, this.getNameHandler());

		// 采用list方式查询，当记录不存在时返回null而不会抛出异常
		List<T> list = jdbcTemplate.query(boundSql.getSql(), this.getRowMapper(clazz), id);
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		return list.iterator().next();
	}

	@Override
	public <T> T get(Criteria criteria, Long id) {
		BoundSql boundSql = SqlAssembleUtils
		    .buildByIdSql(null, id, criteria, this.getNameHandler());

		// 采用list方式查询，当记录不存在时返回null而不会抛出异常
		List<T> list = (List<T>) jdbcTemplate.query(boundSql.getSql(),
		    this.getRowMapper(criteria.getEntityClass()), id);
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		return list.iterator().next();
	}

	@Override
	public <T> T querySingleResult(T entity) {
		BoundSql boundSql = SqlAssembleUtils.buildQuerySql(entity, null, this.getNameHandler());

		// 采用list方式查询，当记录不存在时返回null而不会抛出异常
		List<?> list = jdbcTemplate.query(boundSql.getSql(), boundSql.getParams().toArray(),
		    this.getRowMapper(entity.getClass()));
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		return (T) list.iterator().next();
	}

	@Override
	public <T> T querySingleResult(Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildQuerySql(null, criteria, this.getNameHandler());
		// 采用list方式查询，当记录不存在时返回null而不会抛出异常
		List<?> list = jdbcTemplate.query(boundSql.getSql(), boundSql.getParams().toArray(),
		    this.getRowMapper(criteria.getEntityClass()));
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		return (T) list.iterator().next();
	}

	@Override
	public byte[] getBlobValue(Class<?> clazz, String fieldName, Long id) {
		String tableName = nameHandler.getTableName(clazz);
		String primaryName = nameHandler.getPKName(clazz);
		String columnName = nameHandler.getColumnName(fieldName);
		String tmp_sql = "select t.%s from %s t where t.%s = ?";
		String sql = String.format(tmp_sql, columnName, tableName, primaryName);
		return jdbcTemplate.query(sql, new Object[] { id }, new ResultSetExtractor<byte[]>() {
			@Override
			public byte[] extractData(ResultSet rs) throws SQLException, DataAccessException {
				if (rs.next()) {
					return rs.getBytes(1);
				}
				return null;
			}
		});
	}

	/**
	 * 获取rowMapper对象
	 *
	 * @param clazz
	 * @return
	 */
	protected <T> RowMapper<T> getRowMapper(Class<T> clazz) {

		if (StringUtils.isBlank(rowMapperClass)) {
			return MyBeanPropertyRowMapper.newInstance(clazz);
		} else {
			return (RowMapper<T>) ClassUtils.newInstance(rowMapperClass);
		}
	}

	/**
	 * 获取名称处理器
	 *
	 * @return
	 */
	protected NameHandler getNameHandler() {

		if (this.nameHandler == null) {
			this.nameHandler = new DefaultNameHandler();
		}
		return this.nameHandler;
	}

	@Override
	public <T> List<T> queryBySQL(String sql, Object[] params, Class<T> clazz) {
		return jdbcTemplate.query(sql, params, new MyBeanPropertyRowMapper(clazz));
	}

	@Override
	public <T> List<T> queryBySQL(String sql, List<Object> params, Class<T> clazz) {
		return jdbcTemplate.query(sql, params.toArray(), new MyBeanPropertyRowMapper(clazz));
	}

	@Override
	public <T> List<T> queryBySQL(String sql, Class<T> clazz) {
		return jdbcTemplate.query(sql, new MyBeanPropertyRowMapper(clazz));
	}

	@Override
	public <T> T queryBySqlForObject(String sql, Class<T> clazz) {
		return jdbcTemplate.queryForObject(sql, clazz);
	}

	@Override
	public <T> T queryBySqlForObject(String sql, Object[] params, Class<T> clazz) {
		return jdbcTemplate.queryForObject(sql, params, clazz);
	}

	@Override
	public <T> T queryBySqlForObject(String sql, List<Object> params,
	    Class<T> clazz) {
		return jdbcTemplate.queryForObject(sql, params.toArray(), clazz);
	}

	@Override
	public void executeBySQL(String sql) {
		// TODO Auto-generated method stub
		jdbcTemplate.update(sql);
	}

	@Override
	public void executeBySQL(String sql, Object[] params) {
		// TODO Auto-generated method stub
		jdbcTemplate.update(sql, params);
	}

	@Override
	public void executeBySQL(String sql, List<Object> params) {
		// TODO Auto-generated method stub
		jdbcTemplate.update(sql, params.toArray());
	}
	
	public Integer insertBySQLAndGetKey(String sql, List<Object> params){
		final String esql = sql;
		final List<Object> eparam = params;
		KeyHolder keyHolder = new GeneratedKeyHolder();  
		 jdbcTemplate.update(new PreparedStatementCreator() {  
		        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
		               PreparedStatement ps = connection.prepareStatement(esql,Statement.RETURN_GENERATED_KEYS);  
		               int i = 1;
		                for(Object o : eparam){
		                	ps.setObject(i, o);  
		                	i++;
		                }
		               return ps;  
		        }  
		    }, keyHolder);  
		return keyHolder.getKey().intValue();
	}

	@Override
	public int[] executeBatchBySQL(final String sql, final List<List<Object>> params) {

		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				List<Object> param = params.get(i);
				for (int j = 0; j < param.size(); j++) {
					ps.setObject(j + 1, param.get(j));
				}
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return params.size();
			}
		});
	}

	@Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void setNameHandler(NameHandler nameHandler) {
		this.nameHandler = nameHandler;
	}

	public void setRowMapperClass(String rowMapperClass) {
		this.rowMapperClass = rowMapperClass;
	}

	public void setDialect(String dialect) {
		this.dialect = dialect;
	}

	public String getDialect() {
		// 如果在applicationContext-resource.xml中没有指定dialect，获取操作线程中的数据源名称
		if (StringUtils.isEmpty(dialect)) {
			setDialect("mysql");
			// String datesource =
			// DatabaseContextHolder.getCustomerType();//当前线程的数据源名称
			// //根据数据源名称定义规则 来区分数据源
			// if(datesource.startsWith("mysql")){
			// setDialect("mysql");
			// }
			// if(datesource.startsWith("oracle")){
			// setDialect("oracle");
			// }
		}
		return dialect;
	}

	@Override
	public List<Map<String, Object>> queryForListMap(String sql) {
		return jdbcTemplate.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> queryForListMap(String sql,
	    List<Object> params) {
		return jdbcTemplate.queryForList(sql, params.toArray());
	}

	@Override
	public List<Map<String, Object>> queryForListMap(String sql, Object[] params) {
		return jdbcTemplate.queryForList(sql, params);
	}

	@Override
	public List<Map<String, Object>> queryForListMap(Criteria criteria) {
		BoundSql boundSql = SqlAssembleUtils.buildListSql(null, criteria, this.getNameHandler());
		return jdbcTemplate.queryForList(boundSql.getSql(), boundSql.getParams().toArray());
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Override
	public String executeProcGetCode(String code, String companyCode) {
		final String scode = code;
		final String scompanyCode = companyCode;
		String param2Value = (String) jdbcTemplate.execute(
		    new CallableStatementCreator() {
			    public CallableStatement createCallableStatement(Connection con) throws SQLException {
				    String storedProc = "{call proc_GetSeriaNo(?,?,?)}";// 调用的sql
				    CallableStatement cs = con.prepareCall(storedProc);
				    cs.setString(1, scode);// 设置输入参数的值
				    cs.setString(2, scompanyCode);
				    cs.registerOutParameter(3, Types.VARCHAR);// 注册输出参数的类型
				    return cs;
			    }
		    }, new CallableStatementCallback() {
			    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				    cs.execute();
				    return cs.getString(3);// 获取输出参数的值
			    }
		    });
		return param2Value;
	}

	@Override
	public List<DBTable> getDataBaseTables() {
		String sql = "select TABLE_NAME from INFORMATION_SCHEMA.Tables where table_schema=(select database())";
		SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
		List<DBTable> tables = new ArrayList<DBTable>();
		while (rowSet.next()) {
			DBTable table = new DBTable();
			table.setTableName(rowSet.getString("TABLE_NAME"));
			tables.add(table);
		}
		return tables;
	}

	@Override
	public List<DBColumn> getColumns(String tableName) {
		String sql = "select 	COLUMN_NAME,"
		    + "ORDINAL_POSITION,"
		    + "COLUMN_DEFAULT,"
		    + "IS_NULLABLE,"
		    + "DATA_TYPE,"
		    + "CHARACTER_MAXIMUM_LENGTH,"
		    + "NUMERIC_PRECISION,"
		    + "COLUMN_KEY,"
		    + "COLUMN_COMMENT,"
		    + "EXTRA "
		    + "from INFORMATION_SCHEMA.Columns where table_name= ? and table_schema=(select database())";
		SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, tableName);
		List<DBColumn> columns = new ArrayList<DBColumn>();
		while (rowSet.next()) {
			DBColumn column = new DBColumn();
			column.setColumnName(rowSet.getString("COLUMN_NAME"));
			column.setOrdinalPosition(rowSet.getInt("ORDINAL_POSITION"));
			column.setColumnDefault(rowSet.getString("COLUMN_DEFAULT"));
			column.setIsNullable(rowSet.getString("IS_NULLABLE"));
			column.setDataType(JdbcTypeResolveToLov.resolverToLOV(rowSet.getString("DATA_TYPE")));
			column.setCharacterMaximumLength(rowSet.getInt("CHARACTER_MAXIMUM_LENGTH"));
			column.setNumericPrecision(rowSet.getInt("NUMERIC_PRECISION"));
			column.setColumnKey(rowSet.getString("COLUMN_KEY"));
			column.setColumnComment(rowSet.getString("COLUMN_COMMENT"));
			column.setExtra(rowSet.getString("EXTRA"));
			columns.add(column);
		}
		return columns;
	}

	@Override
	public List<Map<String, Object>> queryForListMapBySql(String sql) {
		ColumnMapRowMapper columnMapRowMapper = new ColumnMapRowMapper();
		List<Map<String, Object>> query = jdbcTemplate.query(sql, columnMapRowMapper);
		return query;
	}
	/*
	 *  
	 * 增加并且获取主键 
	 * @param sql sql语句 
	 * @param params 参数 
	 * @return 主键 
	 */  
	public  Long insertAndGetKey(final String sql, final List<Object> objects) {  
	    KeyHolder keyHolder = new GeneratedKeyHolder();  
	    jdbcTemplate.update(new PreparedStatementCreator() {  
	        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
	              
	            //String sql_sms = "insert into  sms(title,content,date_s,form,sffs,by1,by2,by3) values (?,?,'"+dates+"',?,?,?,?,?)";   
	               PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	               int size = objects.size() ;
	               for (int i = 0; i < size; i++) {
	            	   ps.setObject(i+1, objects.get(i));  
				   }
	               return ps;  
	        }  
	    }, keyHolder);  
	      
	    Long generatedId = keyHolder.getKey().longValue();   
	    return generatedId;  
	}  

}
